﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using SWDataDB.Models;
using SWDataDB.DAL;


namespace SWDataDB.DAL
{
    public class UserTbl
    {
        public UserTbl() { }
        /// <summary>
        /// 获取所有用户的信息
        /// </summary>
        public DataTable GetAllUser()
        {
            string strSql = "SELECT * FROM dbo.USER_TBL";
            return SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }
        public DataSet GetAllUserDS()
        {
            string strSql = "SELECT * FROM dbo.USER_TBL";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        public USER_TBL SelectUser(string strLogin)
        {
            USER_TBL userInf = new USER_TBL();
            string strSql = "SELECT * FROM dbo.USER_TBL WHERE LOGIN_NAME='";
            strSql += strLogin.ToString() + "'";

            DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
            if (dt.Rows.Count < 1)
                return null;
            try
            {
  
                foreach(DataRow dr in dt.Rows)
                {
                    userInf.O_USER_ID = Convert.ToInt32(dr[0].ToString());
                    userInf.O_LOGIN_NAME = dr[1].ToString();
                    userInf.O_PASSWORD = dr[2].ToString();
                    userInf.O_REAL_NAME = dr[3].ToString();
                    userInf.O_GROUP = Convert.ToInt32(dr[4].ToString());
                }

            }
            catch (FormatException e)
            {
                throw e;
            }
            catch (OverflowException e)
            {
                throw e;
            }

            return userInf;
            
        }





        public bool SelectUser(Int32 UserGroup, string strUser, string strPsw)
        {
            string strSql = "SELECT * FROM dbo.USER_TBL WHERE LOGIN_NAME='" + strUser + "'AND PASSWORD='" + strPsw + "'AND USERGROUP='" + UserGroup + "'";
            DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
            if (dt.Rows.Count > 0)
                return true;

            return false;
        }



        //根据ID选择用户
        public int SelUserByID(long UserId)
        {
            string strSql = "select FROM dbo.USER_TBL WHERE USER_ID =  " + UserId.ToString();
            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }


        public bool IsUserExist(string strUser)
        {
            string strSql = "SELECT * FROM dbo.USER_TBL WHERE LOGIN_NAME='" + strUser + "'";
            DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
            if (dt.Rows.Count > 0)
                return true;

            return false;
        }
        public bool IsUserGroup(Int32 UserGroup,string strUser)
        {
            string strSql = "SELECT * FROM dbo.USER_TBL WHERE USERGROUP='" + UserGroup + "'and LOGIN_NAME='" + strUser + "'";
            DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
            if (dt.Rows.Count > 0)
                return true;

            return false;
        }
        public int AddUser(string strUser, string strPsw, string strReal, int nGroup)
        {
            StringBuilder sbSql = new StringBuilder("INSERT INTO dbo.USER_TBL(LOGIN_NAME,PASSWORD,REAL_NAME,USERGROUP) VALUES(");

            sbSql.AppendFormat("'{0}',", strUser);
            sbSql.AppendFormat("'{0}',", strPsw);
            sbSql.AppendFormat("'{0}',", strReal);
            sbSql.Append("1)");

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, sbSql.ToString(), null);
        }

        /// <summary>
        /// 添加用户
        /// </summary>
        /// <param name="userInf"></param>
        /// <returns></returns>
        public int AddUser(USER_TBL userInf)
        {
            string strSql = "INSERT INTO dbo.USER_TBL VALUES(";
            strSql += "'" + userInf.O_LOGIN_NAME + "'"+ ",";
            strSql += "'" + userInf.O_PASSWORD + "'"+ ",";
            strSql += "'" + userInf.O_REAL_NAME + "'"+ ",";
            strSql += userInf.O_GROUP + ")"; 
            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 更新用户信息(主要是密码),(用户名一旦申请，就不能改变)
        /// </summary>
        /// <param name="userInf"></param>
        /// <returns></returns>
        public int UpdateUser(string strLogin, string strPsw)
        {
            string strSql = "UPDATE dbo.USER_TBL VALUES SET PASSWORD = '" + strPsw + "' ";
            strSql += "WHERE LOGIN_NAME = '"+ strLogin + "'";
            
            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        //根据ID更新用户
        public int UpdateUser(int UID,string strPsw,string UName,string RName,int UGROUP)
        {
            string strSql = "UPDATE dbo.USER_TBL  SET PASSWORD = '" + strPsw + "',LOGIN_NAME='" + UName + "',REAL_NAME='" + RName + "',USERGROUP=" + UGROUP + "WHERE USER_ID=" + UID;
             

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        public int UpdateUser(long userId, string strPsw)
        {
            string strSql = "UPDATE dbo.USER_TBL VALUES SET PASSWORD = '" + strPsw + "' ";
            strSql += "WHERE USER_ID = " + userId.ToString() + "";

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 更具UserID删除用户
        /// </summary>
        /// <param name="UserId"></param>
        /// <returns></returns>
        public int DelUser(long UserId)
        {
            string strSql = "DELETE FROM dbo.USER_TBL WHERE USER_ID =  " + UserId.ToString();
            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 根据用户名删除用户
        /// </summary>
        /// <param name="strLogin"></param>
        /// <returns></returns>
        public int DelUser(string strLogin)
        {
            string strSql = "DELETE FROM dbo.USER_TBL WHERE USER_ID =  " + strLogin;
            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }
    }
}
